<?php
namespace Home\Model;
use Think\Model;
use Think\Db\Driver\Sqlsrv;
class CzjComModel extends Model {

	public function getRow($where='',$order='comid asc'){

		
		$sql="select top 1 * from czj_com";
		if(!empty($where)) $sql.=" where ".$where;
		$sql.=" order by ".$order;

		$rows = $this->query($sql);

		return $rows[0];
	}
	
	public function getAllRows($where='',$order='comid asc',$use_cache=false)
	{
		$sql="select * from czj_com";
		if(!empty($where)) $sql.=" where ".$where;
		$sql.=" order by ".$order;
		//echo $sql;
		$rows = $this->query($sql);
		return $rows;
		/*
		$cache_key = md5($where.$order);
		$data=S($cache_key);
		if(empty($data) || !$use_cache)
		{
			$sql="select * from czj_com";
			if(!empty($where)) $sql.=" where ".$where;
			$sql.=" order by ".$order;
			//echo $sql;
			$rows = $this->query($sql);
			S($cache_key,$rows);
		}

		
		return $data;
		*/
	}

/**
 * 根据企业名称查询
 * @param  [type] $name [description]
 * @return [type]       [description]
 */
	public function findByName($name,$comid=null)
	{
		$where="com_name = '$name'";
		if(!empty($comid)) $where.=" and comid <> ".$comid;
		return $this->getRow($where);
	}
/**
 * 根据识别号查询
 * @param  [type] $sbh [description]
 * @return [type]      [description]
 */
	public function findBySbh($sbh,$comid=null)
	{
		$where="com_nsrsbh = '$sbh'";
		if(!empty($comid)) $where.=" and comid <> ".$comid;
		return $this->getRow($where);
	}


	public function findInfo($sbh,$name)
	{
		if(empty($sbh)) return false;
		$row=$this->findBySbh($sbh);
		if(empty($row))
			$row=$this->findByName($name);
		return $row;
	}


	public function findById($comid)
	{
		$where="comid = '$comid'";
		
		return $this->getRow($where);
	}

	public function findByJgdmz($jgdmz)
	{
		if(empty($jgdmz)) return array();
		$where="com_nsrsbh like '%$jgdmz%'";
		return $this->getAllRows($where);
	}


	
	/**
	 * 查询某企业id有多少条税收数据
	 * @param  [type]  $comid    企业id
	 * @param  boolean $is_count 是否是统计数据,默认是true表示只需要统计记录数
	 * @return [type]            [description]
	 */
	public function getSScount($comid,$is_count=true)
	{
		if($is_count) $fiels="count(ssid) as ts";
		else $fiels="*";
		$sql="select ".$fiels." from czj_sszsxx where zscom=".$comid;
		//echo $sql;
		$rows = $this->query($sql);
		//print_r($rows);
		if($is_count) return $rows[0][ts];
		else return $rows;
		
	}

	public function getSStjInfo($comid)
	{
		$sshj=array();
		for($i=2006;$i<=2016;$i++) $sshj[$i]=0;


		$sql="SELECT YEAR(zssj) AS zssj_year, COUNT(ssid) AS ts, SUM(zsje) AS hjje
				FROM czj_sszsxx
				WHERE (zscom = ".$comid.")
				GROUP BY YEAR(zssj)";
		$rows = $this->query($sql);
		foreach($rows as $row)
		{
			$sshj[$row['zssj_year']]=round($row['hjje'],2);
		}
		return implode(',',$sshj);
	}



	
	//更新税收记录的所属企业id
	public function mergeCompany($from_id,$to_id)
	{
		//先合并企业信息.
		//检查其他表中所有所属企业id的也做变更
		//
		//
		$file_name='mergeCompany';
		F("now_up_log_name",$file_name);
		addnewLog('<=====企业合并:'.$from_id.'_to_'.$to_id.'=========>',$file_name);

		
		$to_com_info=$this->findById($to_id);
		
		$from_com_info=$this->findById($from_com_info);
		$up_str="";
		foreach($from_com_info as $key=>$value)
		{
			if(empty($to_com_info[$key])){
				$to_com_info[$key]=$value;
				$up_str.="$key字段内容更新为:$value;";
			}
		}
		if(!empty($up_str))
		{
			$this->where(array('comid'=>$comid))->save($to_com_info);
			addUpLog($to_id."企业信息变更内容:".$up_str);
			addnewLog($to_id."企业信息变更内容:".$up_str,$file_name);
		}

		//更新其他表数据
		$this->upComidOtherTb('com_bzxx','bzxxid',$from_id,$to_id);
		$this->upComidOtherTb('czj_com_notice','nid',$from_id,$to_id);
		$this->upComidOtherTb('czj_com_zdy','com_zdy_id',$from_id,$to_id,'com_id');
		$this->upComidOtherTb('czj_deptocom','dtcid',$from_id,$to_id,'comid');
		$this->upComidOtherTb('czj_itemtocom','itemtocomid',$from_id,$to_id,'comid');
		$this->upComidOtherTb('czj_sszsxx','ssid',$from_id,$to_id,'zscom');
		$this->upComidOtherTb('czj_tjssxx','tjssid',$from_id,$to_id,'comid');
		$this->upComidOtherTb('PaymentRequisition','prid',$from_id,$to_id,'comid');
		$this->upComidOtherTb('SinoProbeInfo','spid',$from_id,$to_id,'comid');
		$this->upComidOtherTb('situation','sid',$from_id,$to_id,'com_id');
		$this->upComidOtherTb('sssjhc','hcid',$from_id,$to_id,'comid');


		//删除旧的企业记录
		if($this->where(array('comid'=>$from_id))->delete())
		{
			addnewLog("企业id:[$from_id]信息被删除!",'del');
		}


	}

/**
 * 更新其他数据表中的所属企业id
 * @param  [type] $table_name [description]
 * @param  [type] $from_id    [description]
 * @param  [type] $to_id      [description]
 * @return [type]             [description]
 */
public function upComidOtherTb($table_name,$pk_key,$from_id,$to_id,$com_key_name='sscomid')
{
	$find_sql="select ".$pk_key." from ".$table_name." where ".$com_key_name."=".$from_id;
	$rows=$this->query($find_sql);

	$file_name=F("now_up_log_name");
	addnewLog("<=====开始更新表[".$table_name."]的相关记录========>",$file_name);
	$up_num=0;
	if(count($rows) > 0)
	{//有数据需要更新
		
		foreach($rows as $row)
		{
			$sql="UPDATE ".$table_name." SET ".$com_key_name."=".$to_id." WHERE ".$pk_key."=".$row[$pk_key];
			if($this->execute($sql)==false)
			{
				addnewLog("企业[$from_id]在表[$table_name]中[$com_key_name]字段更新失败;[$sql]");
			}
			else
			{
				addnewLog("企业[$from_id]在表[$table_name]中主键值为[".$row[$pk_key]."]的记录[$com_key_name]字段更新为[$to_id];",$table_name);
				$up_num++;
			}

		}
		
	}
	else
	{
		addnewLog("企业[$from_id]在表[$table_name]中,没有需要更新的记录;");
	}
	addnewLog("<====表[".$table_name."]的更新完毕.共更新了".$up_num."条记录========>",$file_name);

}

	
	//更新企业名称
	public function upComName($comid,$new_name)
	{
		//更新名字前先看一下要变更的名字是否已存在
		$old_row=$this->findByName($new_name,$comid);
		if(!empty($old_row))
		{
			addnewLog("企业名称:[$new_name],已存在.[$comid]无法变更!");
			
			return false;
		}

		$state=$this->where(array('comid'=>$comid))->save(array('com_name'=>$new_name));
		if($state==false) addnewLog($comid."企业名称更新失败!");
		else  addUpLog($comid."企业名称更新为:$new_name");
		return $state;
	}
	/**
	 * 更新指定企业的识别号
	 * @param  [type] $comid    [description]
	 * @param  [type] $new_name [description]
	 * @return [type]           [description]
	 */
	public function upComSbh($comid,$new_sbh)
	{
		//更新名字前先看一下要变更的识别号是否已存在
		$old_row=$this->findBySbh($new_sbh,$comid);
		if(!empty($old_row))
		{
			addnewLog("企业识别号:[$new_sbh]已存在.[$comid]无法变更!");

			return false;
		}

		$state=$this->where(array('comid'=>$comid))->save(array('com_nsrsbh'=>$new_sbh));
		if($state==false) addnewLog($comid."企业识别号更新失败!");
		else  addUpLog($comid."企业识别号更新为:$new_sbh");
		return $state;
	}


/**
 * 根据企业ID.变更企业识别号
 * @param  [type] $comid   [description]
 * @param  [type] $new_sbh [description]
 * @return [type]          [description]
 */
	public function upSbhBycomid($comid,$new_sbh,$old_sbh='')
	{

		echo "企业id:".$comid."的识别号变更为:".$new_sbh.";";
		if(!empty($old_sbh)) echo "原识别号是:".$old_sbh;
		echo "\r\n";

		$state=$this->where(array('comid'=>$comid))->setField('com_nsrsbh',$new_sbh);
		if($state != false )
		{
			$upSbhBycomid_log=F("upSbhBycomid_log");
			if($upSbhBycomid_log == false ) $upSbhBycomid_log=array();
			$upSbhBycomid_log[]=array('comid'=>$comid,'new_sbh'=>$new_sbh,'old_sbh'=>$old_sbh);
			F("upSbhBycomid_log",$upSbhBycomid_log);
		}

	}


/**
 * 多余企业数据合并.把from_comid的企业相关数据.变更到to_comid上.并删除from_comid的企业数据
 * @param  [type] $from_comid [description]
 * @param  [type] $to_comid   [description]
 * @return [type]             [description]
 */
	public function merge_com($from_comid,$to_comid)
	{
		//看一下错误的企业id下面有多少税收数据
		$ss_rows=$this->getSScount($from_comid,false);
		$ssts=count($ss_rows);
		
		echo "企业id:".$from_comid."下有税收数据".$ssts."\r\n";
		$state=$this->where(array("zscom"=>$from_comid))->fetchSql(true)->save(array("zscom"=>$to_comid));//setField("zscom",$to_comid);
		echo $state;//table("czj_sszsxx")->
		die();
		if($state != false )
		{
			F("merge_com_".$from_comid,$ss_rows);
			echo "企业id:".$from_comid."下有税收数据成功迁移到id:".$to_comid."下;\r\n";
		}
		else
		{
			echo "出错了!!!".$this->getDbError();
			die();
		}
	}

	public function addNewCom($nsrsbh,$nsrmc)
	{
		return $this->data(array('com_name'=>$nsrmc,'com_nsrsbh'=>$nsrsbh))->add();
	}
}